一、搭建本地 Apache + MySQL 服务器：
- 下载 XAMPP，如安装到 D:\xampp 目录下；
- 下载 phpMyAdmin，放到 D:\xampp 目录下；
- 双击 D:\xampp\xampp-control.exe，然后打开 Apache 和 MySQL，一个本地带有数据库的服务就搭建好了；
- 点击 MySQL 对应的 Admin 按钮，就能在本地浏览 phpMyAdmin 界面了，可以直观观察到对于数据库的操作。
Xampp Control Panel
phpMyAdmin
二、初始化模块
npm init
npm i --save express mysql
npm i nodemon -g // 用于实时监听 Node 的变化
mysqljs/mysql 能够让我们在 node 环境下运行 mysql，使用方式如下：
var mysql = require('mysql');
var connection = mysql.createConnection({
host : 'localhost',
user : 'me',
password : 'secret',
database : 'my_db'
});
connection.connect();
connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
if (error) throw error;
console.log('The solution is: ', results[0].solution);
});
connection.end();
三、Node 连接 MySQL
- 创建数据库并连接
const express = require('express');
const mysql = require('mysql');
// Create connection
const db = mysql.createConnection({
host : 'localhost',
user : 'root',
password : '',
// database: 'nodemysql' // 等创建完数据库再添加
});
// Connect
db.connect((err) => {
if(err) {
throw(err);
}
console.log('MySql Connected...')
});
const app = express();
// Create DB
app.get('/createdb', (req, res) => {
let sql = 'CREATE DATABASE nodemysql';
db.query(sql, (err, result) => {
if(err) throw err;
console.log(result);
res.send('Database created...');
})
});
// Delete DB
/* app.get('/dropdb', (req, res) => {
let sql = 'DROP DATABASE nodemysql';
db.query(sql, (err, result) => {
if(err) throw err;
console.log(result);
res.send('Database droped...');
});
}); */
app.listen(3000, () => {
console.log('Server started on port 3000');
});
database字段要等创建完数据库再添加。
在控制台运行 nodemon app，
在浏览器输入 http://localhost:3000/createdb ，便成功创建了数据库：
createdb
nodemysql
- 表格的增删改查：
const express = require('express');
const mysql = require('mysql');
// Create connection
const db = mysql.createConnection({
host : 'localhost',
user : 'root',
password : '',
database: 'nodemysql' // 要连接的数据库
});
// Connect
db.connect((err) => {
if(err) {
throw(err);
}
console.log('MySql Connected...')
});
const app = express();
// Create table
app.get('/createpoststable', (req, res) => {
let sql = 'CREATE TABLE posts(id int AUTO_INCREMENT, title VARCHAR(255), body VARCHAR(255), PRIMARY KEY(id))';
db.query(sql, (err, result) => {
if(err) throw err;
console.log(result);
res.send('Post table created...');
});
});
// Insert post 1
app.get('/addpost1', (req, res) => {
let post = { title: 'Post One', body: 'This is post number one' };
let sql = 'INSERT INTO posts SET ?'; // ? 号是为 post 留的占位符
let query = db.query(sql, post, (err, result) => {
if(err) throw err;
console.log(result);
res.send('Post 1 added...');
});
});
// Insert post 2
app.get('/addpost2', (req, res) => {
let post = { title: 'Post Two', body: 'This is post number one' };
let sql = 'INSERT INTO posts SET ?'; // ? 号是为 post 留的占位符，作为 db.query 的参数
let query = db.query(sql, post, (err, result) => {
if(err) throw err;
console.log(result);
res.send('Post 2 added...');
});
});
// Select posts
app.get('/getposts', (req, res) => {
let sql = 'SELECT * FROM posts';
let query = db.query(sql, (err, results) => {
if(err) throw err;
console.log(results);
res.send('Posts fetched...');
});
});
// Select single post
app.get('/getpost/:id', (req, res) => {
let sql = `SELECT * FROM posts WHERE id = ${req.params.id}`;
let query = db.query(sql, (err, result) => {
if(err) throw err;
console.log(result);
res.send('Post fetched...');
});
});
// Update post
app.get('/updatepost/:id', (req, res) => {
let newTitle = 'Updated Title';
let sql = `UPDATE posts SET title = '${newTitle}' WHERE id = ${req.params.id}`;
let query = db.query(sql, (err, result) => {
if(err) throw err;
console.log(result);
res.send('Post updated...');
});
});
// Delete post
app.get('/deletepost/:id', (req, res) => {
let sql = `DELETE FROM posts WHERE id = ${req.params.id}`;
let query = db.query(sql, (err, result) => {
if(err) throw err;
console.log(result);
res.send('Post deleted...');
});
});
app.listen(3000, () => {
console.log('Server started on port 3000');
});
可以依次在本地浏览器中进行请求，然后查看数据库的数据～～
